package com.yunqi.sql;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.yunqi.sql.entity.ColumnData;
import com.yunqi.sql.entity.TableData;
import com.yunqi.sql.util.JdbcUtil;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * @author wukq
 * @create 2021/5/13 17:30
 * @decription: 导出数据库里面的所有字段, excel格式
 */
@Slf4j
public class GenerateOpengaussExcel {

    /**
     * 有些数据库是空的,里面没有表,导出来的文件打不开,代码还有待优化
     */
    @Test
    public void testMysql() throws Exception {
        List<String> dbList = new ArrayList<>();
        //List<String> testDBlist = new ArrayList<>();
        List<Map<String, Object>> tableList = new ArrayList<>();
        List<String> tables = new ArrayList<>();

        Connection mySQLConnection = JdbcUtil.getMySQLConnection();
        Statement statement = mySQLConnection.createStatement();

        dbList.add("public");
        //testDBlist.add("ykt_inform");
        for (String item : dbList) {
            String fileName = "E://" + item + "_" + System.currentTimeMillis() + ".xlsx";

            ExcelWriter excelWriter = EasyExcel.write(fileName, ColumnData.class).build();
            WriteSheet totalSheet = EasyExcel.writerSheet(0,item + "表设计").needHead(Boolean.FALSE).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(1,item + "表设计概览").needHead(Boolean.FALSE).build();
            int i = 1;

            statement = mySQLConnection.createStatement();
            //ResultSet tableResult = statement.executeQuery("select TABLE_NAME tableName,TABLE_COMMENT tableComment from TABLES where TABLE_SCHEMA = '" + item + "'");
            ResultSet tableResult = statement.executeQuery("select t.table_name as tableName,\n" +
                    "obj_description(c.relfilenode, 'pg_class') AS tableComment\n" +
                    "from information_schema.tables t \n" +
                    "left join \n" +
                    "pg_class c on t.table_name = c.relname\n" +
                    "where t.table_schema = 'public' and t.table_type='BASE TABLE'");


            List<TableData> tableDataList = new ArrayList<>();
            int count = 0;
           while(tableResult.next()){
                String tableName =  tableResult.getString("tableName");
                String tableComment = tableResult.getString("tableComment");
                TableData data = new TableData(count,tableName,tableComment,"");
                tableDataList.add(data);
               count += 1;
            }
            WriteTable myTable = EasyExcel.writerTable(i - 1).needHead(Boolean.TRUE).build();
            excelWriter.write(tableDataList, totalSheet,myTable);
            Iterator<TableData> iterator = tableDataList.iterator();

            //写所有表
            while (iterator.hasNext()) {
                TableData tableData = iterator.next();

                String tableName = tableData.getTableName();
                String tableComment = tableData.getCnName();
                Map<String, Object> map = new HashMap<>();
                map.put("tableName", tableName);
                map.put("tableComment", tableComment);
                tableList.add(map);
                tables.add(tableName);
                // log.info(" 表名:tableName" + tableName + "  tableComment" + tableComment);

                WriteTable writeTable = EasyExcel.writerTable(i - 1).needHead(Boolean.TRUE).build();

                int j = 1;
                List<ColumnData> list = new ArrayList<>();
                statement = mySQLConnection.createStatement();
                String sql = "select c.COLUMN_NAME columnName,c.data_type dataType,c.character_maximum_length characterMaximumLength,\n" +
                        "c.udt_name columnType ,c.IS_NULLABLE isNullable ,c.COLUMN_DEFAULT columnDefault ,\n" +
                        "a.column_comment columnComment\n" +
                        "from information_schema.columns c\n" +
                        "left join (\n" +
                        "SELECT  \n" +
                        "    attname AS column_name,  \n" +
                        "    pg_catalog.col_description(relfilenode, attnum) AS column_comment  \n" +
                        "FROM  \n" +
                        "    pg_catalog.pg_attribute  \n" +
                        "JOIN pg_catalog.pg_class ON attrelid = pg_class.oid  \n" +
                        "JOIN pg_catalog.pg_namespace ON relnamespace = pg_namespace.oid  \n" +
                        "WHERE  \n" +
                        "    relkind = 'r'            \n" +
                        "    AND attnum > 0          \n" +
                        "    AND NOT attisdropped     \n" +
                        "    AND pg_namespace.nspname = 'public'      \n" +
                        "    AND pg_class.relname = '" + tableName + "' \n" +
                        ") a on c.COLUMN_NAME = a.column_name\n" +
                        "where c.table_schema='public'\n" +
                        "and c.table_name ='" + tableName + "' \n" +
                        "order by c.ordinal_position";
                log.info("-----------sql = "+ sql);
                ResultSet columnResult = statement.executeQuery(sql);
                while (columnResult.next()) {

                    String columnName = columnResult.getString("columnName");
                    String dataType = columnResult.getString("dataType");
                    String characterMaximumLength = columnResult.getString("characterMaximumLength");
                    String columnType = columnResult.getString("columnType");
                    String columnComment = columnResult.getString("columnComment");
                    String isNullable = columnResult.getString("isNullable");
                    String columnDefault = columnResult.getString("columnDefault");
                    //log.info(" 列名: columnName" + columnName + "  dataType" + dataType + "  characterMaximumLength" + characterMaximumLength + "  columnType" + columnType
                    //        + "  columnComment" + columnComment);

                    ColumnData columnData = new ColumnData();
                    columnData.setNum(j);
                    columnData.setComment(columnComment);
                    columnData.setName(columnName);
                    columnData.setEmpty(isNullable);
                    columnData.setNote(columnType);
                    columnData.setDefaultValue(columnDefault);
                    if (dataType.equalsIgnoreCase("character varying")) {
                        columnData.setLength(characterMaximumLength);
                    } else if (dataType.equalsIgnoreCase("bigint") || dataType.equalsIgnoreCase("tinyint")
                            || dataType.equalsIgnoreCase("int") || dataType.equalsIgnoreCase("smallint")) {

                        String length = columnType.substring(columnType.indexOf("int") + 3);
                        columnData.setLength(length);
                    }
                    j += 1;
                    list.add(columnData);

                }
                i += 1;
                ColumnData columnData = new ColumnData();
                columnData.setComment("表名:" + tableName + " 表备注:" + tableComment);

                ColumnData columnData2 = new ColumnData();
                list.add(columnData);
                list.add(columnData2);
                list.add(columnData2);
                list.add(columnData2);
                excelWriter.write(list, writeSheet, writeTable);
            }
            excelWriter.finish();
        }
        statement.close();
        mySQLConnection.close();
    }

}
